<?php if (!defined("_ECRIRE_INC_VERSION")) return; if (class_exists('ProductExcel')) return;
/**
 * EcataloguE
 *
 * @author     Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
 * @license    GNU/GPL
 * @package    plugins
 * @subpackage ecatalogue
 * @copyright  (c) Aleksey Yaroshchuk 2011-2012
 * @link       http://www.spip-contrib.net/EcataloguE
 */



/**
 *  This class maintain Excel download/upload operations with products 
 *
 *  @author Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com) 
 */

class ProductExcel {   
    private $product_mdl;
    private $article_mdl;
    private $rubrique_mdl;
    private $mot_article_mdl;
    
    /**
     *  Boolean value wich tells us wether status update is needed when updating from Excel 
     */
    public $UpdateStatus = false;
    
    /**
     *  Array of rubriques, articles from wich must be downloaded in Excel file
     */
    public $DownloadRubriques = array();
    
    /**
     *  ID of Mot, articles with wich will be downloaded
     */
    public $DownloadIdMot = false;
    
    public function __construct() {
        include_spip('functions/pdk_common');
        include_spip('spip_bonux_fonctions');
        
        $this->product_mdl = pdk_load_class('ProductModel','classes/');
        $this->article_mdl = pdk_load_class('PdkArticleModel','classes/models');
        $this->rubrique_mdl = pdk_load_class('PdkRubriqueModel','classes/models');
        $this->mot_article_mdl = pdk_load_class('PdkMotArticleModel','classes/models');
        
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Writer.php";
        
        $this->UpdateStatus = lire_config('ecatalogue/excel_update_status');
        $this->DownloadRubriques = picker_selected(lire_config('ecatalogue/branches'), 'rubrique');
    }
    
    public function OutputExcel() {
        // Creating a workbook
        $workbook = new Spreadsheet_Excel_Writer();    
        $workbook->setTempDir(_DIR_CACHE);
        $workbook->setVersion(8);
        
        // sending HTTP headers
        $workbook->send('products.xls');
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    
        // After file has been sent, just exit:
        exit;
    }
    
    /**
     *  Gets tmp directory full system path
     */
    public function GetTmpDir() {
        $dir = str_replace(DIRECTORY_SEPARATOR.'ecrire','',getcwd());        
        $dir = $dir.DIRECTORY_SEPARATOR.'tmp/'; 
        return $dir; 
    }
    
    /**
     *  @param string $filename Filename (needed for unit testing)
     */
    public function BackupExcel($filename = '') {        
        // Creating a workbook
        if  ($filename)
            //$workbook = new Spreadsheet_Excel_Writer(_DIR_TMP.$filename);
            $workbook = new Spreadsheet_Excel_Writer($this->GetTmpDir().$filename);
        else
            //$workbook = new Spreadsheet_Excel_Writer(_DIR_TMP.'products_'.date("d-m-Y_H-i-s").'.xls');    
            $workbook = new Spreadsheet_Excel_Writer($this->GetTmpDir().'products_'.date("d-m-Y_H-i-s").'.xls');
        
        $workbook->setVersion(8);
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    }
    
    /**
     *  Adds Worksheet with clients to given $workbook
     */
    private function addWorksheet(&$workbook) {
        // Creating worksheet    
        $worksheet =& $workbook->addWorksheet('Products');
        $worksheet->setInputEncoding('utf-8');
        
        // Format for first line
        $format_bold =& $workbook->addFormat();
        $format_bold->setBold();
        
        $worksheet->write(0, 0, _T('ecatalogue:id_article'), $format_bold);
        $worksheet->write(0, 1, _T('ecatalogue:sku'), $format_bold);
        $worksheet->write(0, 2, _T('ecatalogue:product_id_rubrique'), $format_bold);
        $worksheet->write(0, 3, _T('ecatalogue:product_rubrique'), $format_bold);
        $worksheet->write(0, 4, _T('ecatalogue:product_name'), $format_bold);
        $worksheet->write(0, 5, _T('ecatalogue:quantity'), $format_bold);
        $worksheet->write(0, 6, _T('ecatalogue:price'), $format_bold);
        $worksheet->write(0, 7, _T('ecatalogue:priceold'), $format_bold);
        
        
        // Set columns width: 
        $worksheet->setColumn(0,5, 15);
        
        $articles = $this->article_mdl->select("statut!='poubelle'",'','id_article');
        
        /*
        $download_rubriques = picker_selected(_request('download_rubriques'), 'rubrique');
        if (!$download_rubriques)
            //$download_rubriques = picker_selected(lire_config('ecatalogue/branches'), 'rubrique');     
            $download_rubriques = $this->DownloadRubriques;
        */
                
        $row_num = 1;
        foreach ($articles as $article) {
            
            $download_rubrique = false;
            if (isset($this->DownloadRubriques) && is_array($this->DownloadRubriques))
                foreach ($this->DownloadRubriques as $id_rubrique) {
                    if ($this->rubrique_mdl->CheckRubriqueInRubrique($article['id_rubrique'], $id_rubrique) || $article['id_rubrique'] == $id_rubrique)
                        $download_rubrique = true;
                }
            if (!isset($this->DownloadRubriques) || !is_array($this->DownloadRubriques) || empty($this->DownloadRubriques) ||
                $download_rubrique) {                    
                    // If we have to download only articles with specified id_mot, check wether  
                    if ($this->DownloadIdMot && !$this->mot_article_mdl->select(array('id_mot='.$this->DownloadIdMot, 'id_article='.$article['id_article'])))
                        continue;
                $product = $this->product_mdl->select_by_id($article['id_article'], true);
                $rubrique = $this->rubrique_mdl->select_by_id($article['id_rubrique']);    
                $worksheet->write($row_num, 0, $article['id_article']);
                $worksheet->write($row_num, 1, $product['sku']);
                $worksheet->write($row_num, 2, $article['id_rubrique']);
                $worksheet->write($row_num, 3, $rubrique['titre']);
                $worksheet->write($row_num, 4, $article['titre']);
                $worksheet->write($row_num, 5, $product['quantity']);            
                $worksheet->write($row_num, 6, $product['price']);
                $worksheet->write($row_num, 7, $product['priceold']);
                $row_num++;
            } 
        }        
    } 
    
    /**
     *  Upload Excel file and update products from it
     *  @param string $primary_key Primary key - used to find article to update. 
     *  @param bool If is set to true, new article will be created in case article isn't found and id_rubrique column has value
     */
    public function UpdateExcel($primary_key='id_article', $create_new=false) {        
        $this->BackupExcel();
        $file_path = $this->uploadExcel();
        $this->updateProductsFromExcel($file_path, $primary_key, $create_new);
        unlink($file_path); // Delete Excel file
    }
    
    /**
     *  Upload Excel file 
     *  @return string File path
     */
    private function uploadExcel() {
        include_spip('classes/pdk_upload');
        $config = array();
        $config['upload_path'] = _DIR_TMP;
        $config['allowed_types'] = 'xls|xlsx'; 
        $config['max_size'] = '2048';
        $config['encrypt_name'] = TRUE;
        $uploader = new pdk_Upload($config);
        
        if (!$uploader->do_upload('excel_products')) 
            throw new Exception(_T('ecatalogue:excel_file_upload_error')); 
            
        $file_data = $uploader->data();
        return $file_data['full_path'];
    }
    
    /**
     *  Update products from given Excel file
     *  @param string $file_path Excel file path
     */
    public function updateProductsFromExcel($file_path, $primary_key='id_article', $create_new=false) {
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Reader.php";
        
        $reader = new Spreadsheet_Excel_Reader();
        $reader->setUTFEncoder('iconv');        
        $reader->setOutputEncoding('UTF-8');
        $reader->read($file_path);
        
        switch ($primary_key) {
            case 'id_article': $col_primary = 1; break;
            case 'sku': $col_primary = 2; break;
            default: $col_primary = 1; break;
        }
        
        foreach($reader->sheets as $k=>$data) {
            // $reader->boundsheets[$k]['name']; // Sheet name            
            foreach($data["cells"] as $row_num=>$row) {                
                if ($row_num == 1) continue; //omit first row 
                $id_article = false;
                $primary_val = $row["$col_primary"]; // First cell has index 1!                
                //if ($primary_val) {
                    $product_data = array();
                    $product_data['sku'] = $row["2"];
                    //$product_data['titre'] = $row["4"];
                    $product_data['quantity'] = $row["6"];
                    $product_data['price'] = $row["7"];
                    $product_data['priceold'] = $row["8"];  
                    
                    $article_data = array();
                    $article_data['titre'] = $row["5"];
                    
                    // Update article status?                    
                    if ($this->UpdateStatus) {
                        $quantity = intval($product_data['quantity']);                        
                        if ($quantity > 0)
                            $article_data['statut'] = 'publie';
                        else
                            $article_data['statut'] = 'prepa';                             
                    }          
                    
                    // To avoid ambiguity select by id if $primary_key = 'id_article'
                    if ($primary_key == 'id_article' && $primary_val) {
                        $product_exist = $this->product_mdl->select_by_id(intval($primary_val));
                    } else if ($primary_val) {
                        $product_exist = $this->product_mdl->select($primary_key." = '".mysql_real_escape_string($primary_val)."'");
                        if ($product_exist) $product_exist = $product_exist[0];
                    } else {
                        $product_exist = false;
                    }
                    
                    if ((!isset($product_exist) || !$product_exist) && $create_new) { // Create new product?
                    
                        $id_rubrique = intval($row['3']);
                        if (!$id_rubrique) continue;                                                
                        $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
                        if (!$rubrique) continue;
                        $article_data['id_rubrique'] = $id_rubrique;
                        $article_data['id_secteur'] = $rubrique['id_secteur'];                        
                        $article_data['statut'] = 'prepa'; // For new article statut must be defined
                        $article_data['lang'] = $rubrique['lang'];
                        
                        // Insert article
                        if (!$id_article = $this->article_mdl->insert($article_data))
                            throw new Exception("Error while inserting article ".$primary_val);
                        
                        // Insert product
                        $product_data['id_article'] = $id_article;                                                
                        if (!$this->product_mdl->insert($product_data))
                            throw new Exception("Error while inserting product #".$id_article);
                            
                    } else if ($product_exist) { // Update old product
                        $id_article = $product_exist['id_article'];
                        
                        // Update product                                                
                        if (!$this->product_mdl->update_by_id($id_article, $product_data)) {                            
                            throw new Exception("Error while updating product #".$id_article);
                        }   
                        
                        // Update article
                        if (!$this->article_mdl->update_by_id($id_article, $article_data))
                            throw new Exception("Error while updating article #".$id_article);                        
                    }
                //}               
            }
        }
    } 
} 
?>